/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.Employee;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import utils.DateFormat;
import utils.Encrypt;
import utils.SQLServerDB;

/**
 *
 * @author vanchung07
 */
public class EmployeeDAO {

    private String lastError;
    private final String SQL_CREATE = "INSERT INTO Employees Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    private final String SQL_READALL = "SELECT * FROM Employees";
    private final String SQL_READBYID = "SELECT * FROM Employees WHERE EmployeeNumber = ?";
    private final String SQL_UPDATE = "UPDATE Employees Set FirstName = ?, LastName= ?, Birthday = ?, Address = ?"
            + "Phone = ?, Email = ?, Gender = ?, WorkExperience = ? WHERE EmployeeNumber =?";
    private final String SQL_DELETE = "UPDATE Employees SET IsDeleted = ? WHERE EmployeeNumber = ?";
    private final String SQL_CHECKLOGIN = "SELECT Password FROM Employees WHERE EmployeeNumber = ?";
    private final String SQL_CHANGEPASS = "UPDATE Employees SET Password = ? WHERE EmployeeNumber = ?";
    private final String SQL_CHANGEROLE = "UPDATE Employees SET RoleID = ? WHERE EmployeeNumber = ?";
    private final String SQL_CHANGEONTRANSFER = "UPDATE Employees SET PostId = ?, ProjectId = ?, DepartmentId = ? WHERE EmployeeNumber = ?";
    private Connection conn;

    public boolean create(Employee e) {
        conn = SQLServerDB.getConn();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_CREATE);
            pr.setString(1, Encrypt.toMD5(e.getPassword()));
            pr.setString(2, e.getFirstName());
            pr.setString(3, e.getFirstName());
            pr.setDate(4, DateFormat.toDate(e.getBirthday().toString(), "yyyy-MM-dd"));
            pr.setString(5, e.getAddress());
            pr.setString(6, e.getPhone());
            pr.setString(7, e.getEmail());
            pr.setInt(8, e.getGender());
            pr.setInt(9, e.getRoleId());
            pr.setInt(10, e.getWorkExperience());
            pr.setBoolean(11, e.isIsDeleted());
            pr.setInt(12, e.getPostId());
            pr.setInt(13, e.getProjectId());
            pr.setInt(14, e.getDepartmentId());
            if (pr.executeUpdate() > 0) {
                setLastError("Create Successful!");
                return true;
            } else {
                setLastError("Error: Create Failed!");
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return false;
    }

    public ArrayList<Employee> ReadAll() {
        conn = SQLServerDB.getConn();
        ArrayList<Employee> list = new ArrayList<>();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_READALL);
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                Employee e = new Employee();
                e.setEmpNum(rs.getInt("EmployeeNumber"));
                e.setPassword(rs.getString("Password"));
                e.setFirstName(rs.getString("FirstName"));
                e.setLastName(rs.getString("FirstName"));
                e.setBirthday(rs.getDate("Birthday"));
                e.setAddress(rs.getString("Address"));
                e.setPhone(rs.getString("Phone"));
                e.setEmail(rs.getString("Email"));
                e.setGender(rs.getInt("Gender"));
                e.setRoleId(rs.getInt("RoleID"));
                e.setWorkExperience(rs.getInt("WorkExperience"));
                e.setIsDeleted(rs.getBoolean("IsDeleted"));
                e.setPostId(rs.getInt("PostId"));
                e.setProjectId(rs.getInt("ProjectId"));
                e.setDepartmentId(rs.getInt("DepartmentId"));
                list.add(e);
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return list;
    }

    public Employee readById(int empNum) {
        conn = SQLServerDB.getConn();
        Employee e = new Employee();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_READBYID);
            pr.setInt(1, empNum);
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                e.setEmpNum(empNum);
                e.setPassword(rs.getString("Password"));
                e.setFirstName(rs.getString("FirstName"));
                e.setLastName(rs.getString("FirstName"));
                e.setBirthday(rs.getDate("Birthday"));
                e.setAddress(rs.getString("Address"));
                e.setPhone(rs.getString("Phone"));
                e.setEmail(rs.getString("Email"));
                e.setGender(rs.getInt("Gender"));
                e.setRoleId(rs.getInt("RoleID"));
                e.setWorkExperience(rs.getInt("WorkExperience"));
                e.setIsDeleted(rs.getBoolean("IsDeleted"));
                e.setPostId(rs.getInt("PostId"));
                e.setProjectId(rs.getInt("ProjectId"));
                e.setDepartmentId(rs.getInt("DepartmentId"));
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return e;
    }

    public boolean update(Employee e) {
        conn = SQLServerDB.getConn();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_UPDATE);
            pr.setString(1, e.getFirstName());
            pr.setString(2, e.getFirstName());
            pr.setDate(3, DateFormat.toDate(e.getBirthday().toString(), "yyyy-MM-dd"));
            pr.setString(4, e.getAddress());
            pr.setString(5, e.getPhone());
            pr.setString(6, e.getEmail());
            pr.setInt(7, e.getGender());
            pr.setInt(8, e.getWorkExperience());
            pr.setInt(9, e.getEmpNum());
            if (pr.executeUpdate() > 0) {
                setLastError("Update Successful!");
                return true;
            } else {
                setLastError("Error: Update Failed!");
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return false;
    }

    public boolean delete(int id) {
        conn = SQLServerDB.getConn();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_DELETE);
            pr.setBoolean(1, true);
            pr.setInt(2, id);
            if (pr.executeUpdate() > 0) {
                setLastError("Delete Successful!");
                return true;
            } else {
                setLastError("Error: Delete Failed!");
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return false;
    }

    public boolean login(int empNum, String password) {
        conn = SQLServerDB.getConn();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_CHECKLOGIN);
            pr.setInt(1, empNum);
            ResultSet rs = pr.executeQuery();
            while (rs.next()) {
                if (Encrypt.toMD5(password).equals(rs.getString("Password"))) {
                    setLastError("Login Successful!");
                    return true;
                } else {
                    setLastError("Error: Login Failed!");
                }
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return false;
    }

    public boolean changePass(int empNum, String password) {
        conn = SQLServerDB.getConn();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_CHANGEPASS);
            pr.setString(1, Encrypt.toMD5(password));
            pr.setInt(2, empNum);
            if (pr.executeUpdate() > 0) {
                setLastError("Update Password Successful!");
                return true;
            } else {
                setLastError("Error: Update Failed!");
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return false;
    }

    public boolean changeRole(int empNum, int roleId) {
        conn = SQLServerDB.getConn();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_CHANGEROLE);
            pr.setInt(1, roleId);
            pr.setInt(2, empNum);
            if (pr.executeUpdate() > 0) {
                setLastError("Update Role uccessful!");
                return true;
            } else {
                setLastError("Error: Update Failed!");
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return false;
    }

    public boolean changeOnTransfer(int empNum, int postId, int projecId, int departmentId) {
        conn = SQLServerDB.getConn();
        try {
            PreparedStatement pr = conn.prepareStatement(SQL_CHANGEONTRANSFER);
            pr.setInt(1, postId);
            pr.setInt(2, projecId);
            pr.setInt(3, departmentId);
            pr.setInt(4, empNum);
            if (pr.executeUpdate() > 0) {
                setLastError("Update uccessful!");
                return true;
            } else {
                setLastError("Error: Update Failed!");
            }
        } catch (SQLException ex) {
            setLastError("Error: " + ex.getMessage());
        } finally {
            SQLServerDB.closeConn(conn);
        }
        return false;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}
